﻿using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using System.Text;
namespace PCSComProduction.WorkOrder.DS
{
    public class TinhGiaThanhKeHoachForKMPDS
    {
        private const string THIS = "PCSComProduction.WorkOrder.DS.TinhGiaThanhKeHoachForKMPDS";
        public DataTable GetOneItem(int pintProductID)
        {
            const string METHOD_NAME = THIS + ".GetOneItem()";
            DataSet dstDetail = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                //string strSql = String.Empty;
                string strSql = "SELECT	DISTINCT '0' AS 'Level',Cast(null as decimal(20, 4)) MaterialExpense "
                     + ",Cast(null as decimal(20, 4)) OutSideExpense,Cast(null as decimal(20, 4)) CostPerMin,Cast(null as decimal(20, 4)) Purchaseunitprice,"
                     + "ITM_Category.Code AS CategoryCode, "
                     + "ITM_Product.Code AS 'Part No.', "
                     + "ITM_Product.Description AS 'Part Name', "
                     + "ITM_Product.Revision AS 'Model', "
                     + "MST_UnitOfMeasure.Code AS 'Stock UM', "
                     + "MST_WorkCenter.Code AS 'ProcessCode', "
                     + "MST_WorkCenter.Name AS 'ProcessName',"
                     + " CAST(NULL AS decimal(18,5)) AS 'BOM Qty',"
                     + " CAST(NULL AS decimal(18,5)) AS 'LeadTimeOffset',"
                     + " CAST(NULL AS decimal(18,5)) AS 'Shrink',"
                     + " MST_Party.Code AS 'Supplier',"
                     + " ITM_Product.ProductID, NULL AS 'ParentID',"
                     + " ITM_Product.MakeItem"
                     + " FROM ITM_Product JOIN ITM_BOM"
                     + " ON ITM_Product.ProductID = ITM_BOM.ProductID"
                     + " LEFT JOIN (SELECT PRO_ProductionLine.Code, PRO_ProductionLine.Name,"
                     + " MST_WorkCenter.WorkCenterID, MST_WorkCenter.ProductionLineID, ITM_Routing.ProductID"
                     + " FROM ITM_Routing JOIN MST_WorkCenter"
                     + " ON ITM_Routing.WorkCenterID = MST_WorkCenter.WorkCenterID"
                     + " LEFT JOIN PRO_ProductionLine"
                     + " ON MST_WorkCenter.ProductionLineID = PRO_ProductionLine.ProductionLineID"
                     + " WHERE ISNULL(MST_WorkCenter.IsMain , 0)= 1) MST_WorkCenter"
                     + " ON ITM_Product.ProductID = MST_WorkCenter.ProductID"
                     + " LEFT JOIN MST_Party"
                     + " ON ITM_Product.PrimaryVendorID = MST_Party.PartyID"
                     + " JOIN MST_UnitOfMeasure"
                     + " ON MST_UnitOfMeasure.UnitOfMeasureID = ITM_Product.StockUMID"
                     + " LEFT JOIN ITM_Category"
                     + " ON ITM_Product.CategoryID = ITM_Category.CategoryID"
                     + " WHERE ITM_Product.ProductID = " + pintProductID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstDetail, PRO_RequiredMaterialDetailTable.TABLE_NAME);

                return dstDetail.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }


        public DataTable GetAllItems()
        {
            const string METHOD_NAME = THIS + ".GetAllItems()";
            DataSet dstDetail = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                //string strSql = String.Empty;
                string strSql = " "
                 +" SELECT	"
                 + "           DISTINCT '0' AS 'Level', ITM_Category.Code AS CategoryCode,Cast(null as decimal(20, 4)) MaterialExpense,Cast(null as decimal(20, 4)) OutSideExpense,Cast(null as decimal(20, 4)) CostPerMin, Cast(null as decimal(20, 4)) Purchaseunitprice,"
	             +"            ITM_Product.Code AS 'Part No.',  "
	             +"           ITM_Product.Description AS 'Part Name'," 
	             +"            ITM_Product.Revision AS 'Model',   "
	             +"           MST_UnitOfMeasure.Code AS 'Stock UM', "  
	             +"           MST_WorkCenter.Code AS 'ProcessCode', "
	             +"            MST_WorkCenter.Name AS 'ProcessName', "
	             +"           ITM_BOM.Quantity AS 'BOM Qty', ITM_BOM.LeadTimeOffset,"
	             +"            ITM_BOM.Shrink, MST_Party.Code AS 'Supplier',"
	             +"           ITM_Product.ProductID, ITM_BOM.ProductID AS 'ParentID'," 
	             +"           ITM_Product.MakeItem "
                 +"       FROM ITM_Product "
                 +"       JOIN ITM_BOM ON ITM_Product.ProductID = ITM_BOM.ComponentID "
                 /*       --LEFT JOIN (SELECT PRO_ProductionLine.Code, PRO_ProductionLine.Name, 
                        --MST_WorkCenter.WorkCenterID, MST_WorkCenter.ProductionLineID, 
                        --ITM_Routing.ProductID FROM ITM_Routing 
                        --JOIN MST_WorkCenter ON  ITM_Routing.WorkCenterID = MST_WorkCenter.WorkCenterID 
                        --LEFT JOIN PRO_ProductionLine 
                        --ON MST_WorkCenter.ProductionLineID = PRO_ProductionLine.ProductionLineID 
                        --WHERE ISNULL(MST_WorkCenter.IsMain , 0)= 1) MST_WorkCenter 
                        --ON ITM_Product.ProductID = MST_WorkCenter.ProductID */
                 +"       INNER JOIN PRO_ProductionLine MST_WorkCenter ON MST_WorkCenter.ProductionLineID=ITM_Product.ProductionLineID "
                 +"       LEFT JOIN MST_Party ON ITM_Product.PrimaryVendorID = MST_Party.PartyID "
                 +"        LEFT JOIN ITM_Category ON ITM_Product.CategoryID = ITM_Category.CategoryID " 
                 +"       JOIN MST_UnitOfMeasure ON MST_UnitOfMeasure.UnitOfMeasureID = ITM_Product.StockUMID " 
                 +"       WHERE ITM_Product.CCNID = " +SystemProperty.CCNID;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstDetail, PRO_RequiredMaterialDetailTable.TABLE_NAME);

                return dstDetail.Tables[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public DataSet GetAtrib()
        {
            const string METHOD_NAME = THIS + ".GetAtrib()";
            DataSet dstDetail = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                //string strSql = String.Empty;
                string strSql = " select ProductID,MaterialExpense,OutSideExpense,CostPerMin,Purchaseunitprice "
                    + " From CST_StandardCost_ForKMP ";
                    

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstDetail, PRO_RequiredMaterialDetailTable.TABLE_NAME);

                return dstDetail;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}
